﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using HZ.Supervise.Model;

namespace HZ.Supervise.DAL
{
    public class DAL_ProcureCatalog
    {
        public static IList<string> GetQualityLevels()
        {
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable("SELECT * FROM TB_QualityLevel");

            List<string> list = new List<string>();
            foreach (DataRow item in dt.Rows)
                list.Add(item["LevellName"].ToString());
            return list;
        }

        public static HZ.Supervise.Model.View.ProcureCatalog GetProcureCatalogByID(string ProcureCatalogID)
        {
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format("SELECT TOP 1 * FROM View_ProcureCatalog WHERE ProcureCatalogID = '{0}'", ProcureCatalogID));
            if (dt.Rows.Count > 0)
            {
                return DAL_Helper.CommonFill<Model.View.ProcureCatalog>(dt.Rows[0]);
            }
            else
            {
                throw new Exception("未查到该药品");
            }
        }
        //public static int SetAsPreciousDrugs(IList<string> ProcureCatalogID, bool IsPrecious)
        //{

        //}
		/// <summary>
		/// 根据条件查询出所有采购目录表中的数据并查询出补贴比例
		/// </summary>
		/// <param name="stwhere"></param>
		/// <param name="currentPage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalRecord"></param>
		/// <returns></returns>
        public static DataTable GetScaleInfo(string stwhere, string areaId, int rel, int currentPage, int pagesize, out int totalRecord)
        {
            totalRecord = 0;
            HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(areaId))
            {
                string sql = "select count(*) from TB_DistributionRatio where DAreaId='" + areaId + "'";
                int n = (int)sh.ExecuteScalar(sql);
                string strselect="";
                if (rel == 0)
                {
                    
                        stwhere += " and  DAreaId='" + areaId + "'";
                        strselect = "select a.ProcureCatalogID as ProcureCatalogID,a.ProductName as ProductName,b.DAreaName as DAreaName,a.MedicineModelName as MedicineModelName,a.Outlookc as Outlookc,a.Factor as Factor,a.Unit as Unit,a.Material as Material,b.Ratio as Ratio,b.DistributionRatioId as DistributionRatioId,b.DAreaId as DAreaId  from [View_ProcureCatalog] as a left join TB_DistributionRatio as b on a.ProcureCatalogID=b.DProcureCatalogid where 1=1  and a.isUsing=1" + stwhere + "and ProcureCatalogID  in (select DProcureCatalogid from TB_DistributionRatio) order by ProductName ";
                }
                else
                {
                    if (n > 0)
                    {
                        stwhere += " and ProcureCatalogID not in (select DProcureCatalogid from TB_DistributionRatio where  DAreaId='" + areaId + "')";
                       
                    }
                    strselect = "  select ProcureCatalogID, ProductName,outlookc, MedicineModelName,Factor,Unit, Material  from [View_ProcureCatalog] where 1=1  and IsUsing=1" + stwhere + " order by ProductName";

                }
                 
                HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
                pdes.CurrentPage = currentPage;
                dt = pdes.GetSpecailPage();
                totalRecord = pdes.TotalCount;
            }
            else 
            {

                string strselect = "";
                if (rel == 0)
                {
                    strselect += "select a.ProcureCatalogID as ProcureCatalogID,a.ProductName as ProductName,b.DAreaName as DAreaName,a.MedicineModelName as MedicineModelName,a.Outlookc as Outlookc,a.Factor as Factor,a.Unit as Unit,a.Material as Material,b.Ratio as Ratio,b.DistributionRatioId as DistributionRatioId,b.DAreaId as DAreaId  from [View_ProcureCatalog] as a left join TB_DistributionRatio as b on a.ProcureCatalogID=b.DProcureCatalogid where 1=1  and a.isUsing=1" + stwhere + "and ProcureCatalogID  in (select DProcureCatalogid from TB_DistributionRatio) order by ProductName ";
                }
                else
                {
                    strselect += "select  ProcureCatalogID, ProductName, MedicineModelName,Outlookc, Factor, Unit, Material from [View_ProcureCatalog] where 1=1 and isusing=1" + stwhere + " order by ProductName";
                }
                HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strselect, pagesize);
                pdes.CurrentPage = currentPage;
                dt = pdes.GetSpecailPage();
                totalRecord = pdes.TotalCount;
            }
           
            return dt;
        }
        /// <summary>
        /// 批量更新与插入数据
        /// </summary>
        /// <param name="distributionRationList"></param>
        /// <param name="distributionRationLogList"></param>
        /// <returns></returns>
        public static int InsertIntoDistributionRatioList(IList<DistributionRatio> distributionRationList, IList<DistributionRatioLog> distributionRationLogList) 
        {
            HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
             try
            {
                sh.BeginTrans();
                foreach (var item in distributionRationList)
                {
                    string sql = string.Format("SELECT COUNT(*) FROM TB_DistributionRatio WHERE DistributionRatioId = '{0}'", item.DistributionRatioId);
                    int n = (int)sh.ExecuteScalar(sql);
                    if (n > 0)
                    {
                        sql = DAL_Helper.GetUpdateSql(item);
                    }
                    else
                    {
                        sql = DAL_Helper.GetInsertSql(item);
                    }
                    sh.ExecuteNonQuery(sql);
                }
                foreach (var item in distributionRationLogList)
                {
                    string sql = DAL_Helper.GetInsertSql(item);
                    sh.ExecuteNonQuery(sql);
                }
                sh.CommitTrans();
                return 1;
            }
             catch (Exception ee)
             {
                 sh.RollTrans();
                 HZ.Common.log4netManager.LogDebug("DAL_ProcureCatalog", ee.Message, ee);
                 return 0;
             }
        }
        /// <summary>
        /// 批量更新全部数据
        /// </summary>
        /// <returns></returns>
        public static int InsertIntoDistributionRatioAll(DistributionRatio distributionRation, DistributionRatioLog distributionRationLog,string where,int res) 
        {
            HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
            try
            {
                sh.BeginTrans();
                //查询该地区是否有数据
                string sql = string.Format("SELECT count(DProcureCatalogid) FROM TB_DistributionRatio WHERE DAreaId = '{0}'", distributionRation.DAreaId);
                int n = (int)sh.ExecuteScalar(sql);
                StringBuilder sb = new StringBuilder();
                //插入
                if (res == 0)
                {
                    if (n == 0)
                    {
                        sb.Append("insert  TB_DistributionRatio (DistributionRatioId,DProcureCatalogid,DistributionTime,DistributionName ,DistributionState,DAreaId,DAreaName ,Ratio) select '" + Guid.NewGuid().ToString().Substring(0, 29) + "'+ProcureCatalogID,ProcureCatalogID ,GETDATE(),'" + distributionRation.DistributionName + "',0,'" + distributionRation.DAreaId + "','" + distributionRation.DAreaName + "','" + distributionRation.Ratio + "'from [View_ProcureCatalog] where 1=1" + where + " and IsUsing=1");
                        sb.Append("insert  TB_DistributionRatioLog (DistributionRatioId,DProcureCatalogid,DistributionTime,DistributionName ,DistributionState,DAreaId,DAreaName ,Ratio) select '" + Guid.NewGuid().ToString().Substring(0, 29) + "'+ProcureCatalogID,ProcureCatalogID ,GETDATE(),'" + distributionRation.DistributionName + "',0,'" + distributionRation.DAreaId + "','" + distributionRation.DAreaName + "','" + distributionRation.Ratio + "'from [View_ProcureCatalog] where  1=1 " + where + " and IsUsing=1");
                    }
                    else
                    {
                        //sb.Append( "update TB_DistributionRatio set DistributionTime=getdate(),DistributionName='" + distributionRation.DistributionName + "',Ratio='" + distributionRation.Ratio + "'where DAreaId=" + distributionRation.DAreaId);
                        sb.Append("insert  TB_DistributionRatio (DistributionRatioId,DProcureCatalogid,DistributionTime,DistributionName ,DistributionState,DAreaId,DAreaName ,Ratio) select '" + Guid.NewGuid().ToString().Substring(0, 29) + "'+ProcureCatalogID,ProcureCatalogID ,GETDATE(),'" + distributionRation.DistributionName + "',0,'" + distributionRation.DAreaId + "','" + distributionRation.DAreaName + "','" + distributionRation.Ratio + "'from [View_ProcureCatalog] where 1=1 " + where + " and IsUsing=1 and ProcureCatalogID not in(SELECT DProcureCatalogid FROM TB_DistributionRatio WHERE DAreaId = '" + distributionRation.DAreaId + "')");
                        sb.Append("insert  TB_DistributionRatioLog (DistributionRatioId,DProcureCatalogid,DistributionTime,DistributionName ,DistributionState,DAreaId,DAreaName ,Ratio) select '" + Guid.NewGuid().ToString().Substring(0, 29) + "'+ProcureCatalogID,ProcureCatalogID ,GETDATE(),'" + distributionRation.DistributionName + "',0,'" + distributionRation.DAreaId + "','" + distributionRation.DAreaName + "','" + distributionRation.Ratio + "'from [View_ProcureCatalog] where 1=1 " + where + " and IsUsing=1 and ProcureCatalogID not in(SELECT DProcureCatalogid FROM TB_DistributionRatio WHERE DAreaId = '" + distributionRation.DAreaId + "')");
                    }
                   
                
                }
                    //更新
                else 
                {
                    sb.Append( "update TB_DistributionRatio set DistributionTime=getdate(),DistributionName='" + distributionRation.DistributionName + "',Ratio='" + distributionRation.Ratio + "'where DAreaId=" + distributionRation.DAreaId);
                    sb.Append("insert  TB_DistributionRatioLog (DistributionRatioId,DProcureCatalogid,DistributionTime,DistributionName ,DistributionState,DAreaId,DAreaName ,Ratio) select '" + Guid.NewGuid().ToString().Substring(0, 29) + "'+DProcureCatalogid,DProcureCatalogid ,GETDATE(),'" + distributionRation.DistributionName + "',0,'" + distributionRation.DAreaId + "','" + distributionRation.DAreaName + "','" + distributionRation.Ratio + "'from TB_DistributionRatio where  DAreaName='" + distributionRation.DAreaName + "'");
                  
                }
               
                   sh.ExecuteNonQuery(sb.ToString());
                sh.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                sh.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_ProcureCatalog", ee.Message, ee);
                return 0;
            }
        }
        public static int SetAsPreciousDrugs(IList<HZ.Supervise.Model.Insurance> list)
        {
            HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
            try
            {
                sh.BeginTrans();
                foreach (var item in list)
                {
                    string sql = string.Format("SELECT COUNT(*) FROM TB_Insurance WHERE ProcureCatalogID = '{0}'", item.ProcureCatalogID);
                    int n = (int)sh.ExecuteScalar(sql);
                    if (n > 0)
                    {
                        sql = DAL_Helper.GetUpdateSql(item);
                    }
                    else
                    {
                        sql = DAL_Helper.GetInsertSql(item);
                    }
                    sh.ExecuteNonQuery(sql);
                }
                sh.CommitTrans();
                return 1;
            }
            catch (Exception ee)
            {
                sh.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_ProcureCatalog", ee.Message, ee);
                return 0;
            }
        }

		  /// <summary>
        /// 将药品信息插入 
        /// </summary>
        /// <param name="blackList">比例</param>
        /// <returns></returns>
        public static int InsertIntoDistributionRatio(HZ.Supervise.Model.DistributionRatio distribution, HZ.Supervise.Model.DistributionRatioLog distributionLog)
        {
            int count = 0;
            try
            {
                HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
                string sql = "select DistributionRatioId from TB_DistributionRatio where DAreaId='" + distribution.DAreaId + "' and DProcureCatalogid='" + distribution.DProcureCatalogid + "' ";
                DataTable dt = sh.ExecuteTable(sql);
                if (dt.Rows.Count > 0)
                {
                    distribution.DistributionRatioId = dt.Rows[0]["DistributionRatioId"].ToString();
                    count = HZ.Data.DAL_Helper.UpdateModel<HZ.Supervise.Model.DistributionRatio>(distribution);
                    count += HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.DistributionRatioLog>(distributionLog);
                }
                else
                {
                    count = HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.DistributionRatio>(distribution);
                    count += HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.DistributionRatioLog>(distributionLog);
                }
            }

            catch
            {
                count = 0;
            }
            return count;
        }
		/// <summary>
		/// 将药品信息更新
		/// </summary>
		/// <param name="blackList">比例</param>
		/// <returns></returns>
		public static int UpdateIntoDistributionRatio(HZ.Supervise.Model.DistributionRatio distribution, HZ.Supervise.Model.DistributionRatioLog distributionLog)
		{
			int count = 0;
			try
			{

				count = HZ.Data.DAL_Helper.UpdateModel<HZ.Supervise.Model.DistributionRatio>(distribution);
				count += HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.DistributionRatioLog>(distributionLog);
			}
			catch
			{
				count = 0;
			}
			return count;
		}
        public static IList<HZ.Supervise.Model.View.ProcureCatalog> GetUnTradeProcureCatalog(HZ.Supervise.Model.Query.ProcureCatalog where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT * FROM dbo.View_ProcureCatalog WHERE ProcureCatalogID NOT IN (SELECT ProcureCatalogID FROM dbo.View_Supervise ) {0}", DAL_Helper.GetWhereCondition(where, true));
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return DAL_Helper.CommonFillList<HZ.Supervise.Model.View.ProcureCatalog>(des.GetSpecailPage());
        }

        public static DataTable GetIsPreciousProcureCatalog(HZ.Supervise.Model.Query.ProcureCatalog where, bool? isprecious, int currentpahe, int pagesize, out int totalrecord)
        {
            string wherestr = "1=1";
            if (isprecious != null)
            {
                if (isprecious.Value)
                    wherestr = "IsPrecious = 1";
                else
                    wherestr = "IsPrecious = 0";
            }
            string sql = string.Format("SELECT * FROM View_PreciousDrugs WHERE {0} {1}", wherestr, DAL_Helper.GetWhereCondition(where, true));
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpahe;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();

        }

        public static DataTable GetUnDistributeCompanyList(HZ.Supervise.Model.Query.UnDistributeCompanyQuery where, int currentpage, int pagesize, out int totalrecord)
        {
            //string sql = string.Format(@"SELECT A.*,c.CompanyName AS CompanyName_PS FROM dbo.TB_Company c INNER JOIN (SELECT CompanyID_PS,PurchaseCount, SUM(PurchaseCount * Price) AS UnDistributeAmount , MIN(DATEDIFF(DAY, PlanEndTime,GETDATE())) AS MinDate, MAX( DATEDIFF(DAY, PlanEndTime,GETDATE())) AS MaxDate,(MAX( DATEDIFF(DAY, PlanEndTime,GETDATE()))-MIN(DATEDIFF(DAY, PlanEndTime,GETDATE()))) as DayCount FROM dbo.View_PurchaseOrderDetail   WHERE OrderDetailState = 1 AND DistributeCount = 0 {0} AND PlanEndTime<'{2}'GROUP BY CompanyID_PS,PurchaseCount) A ON c.CompanyID = A.CompanyID_PS ORDER BY {1}", DAL_Helper.GetWhereCondition(where, false), where.SortFields, DateTime.Now);
            string AreaID = where.AreaID;
            string comp = where.CompanyName_PS;
            where.AreaID = null;
            where.CompanyName_PS = null;
            //string sql = string.Format(@"SELECT A.*,c.CompanyName AS CompanyName_PS FROM dbo.TB_Company c INNER JOIN (SELECT CompanyID_PS,Sum(PurchaseCount) AS PurchaseCount, SUM(PurchaseCount * Price) AS UnDistributeAmount , MIN(DATEDIFF(DAY, PlanEndTime,GETDATE())) AS MinDate, MAX( DATEDIFF(DAY, PlanEndTime,GETDATE())) AS MaxDate,(MAX( DATEDIFF(DAY, PlanEndTime,GETDATE()))-MIN(DATEDIFF(DAY, PlanEndTime,GETDATE()))) as DayCount FROM dbo.View_PurchaseOrderDetail WHERE OrderDetailState = 1 AND DistributeCount = 0 {0} AND PlanEndTime<'{2}'GROUP BY CompanyID_PS) A ON c.CompanyID = A.CompanyID_PS ORDER BY {1}", DAL_Helper.GetWhereCondition(where, false), where.SortFields, DateTime.Now);
            //            (CenSubmiitTime IS NOT NULL AND datediff(ss,CenSubmiitTime,CompanyValidateWarehouseTime) > 172800 ) OR ( CenSubmiitTime IS NULL AND datediff(ss,CenSubmiitTime,GETDATE())>172800)

            string sql = string.Format(@"
            SELECT a.CompanyID_PS,MAX(a.CompanyName_PS) CompanyName_PS, Sum(a.PurchaseCount-a.DistributeCount) AS PurchaseCount, 
            SUM((a.PurchaseCount-a.DistributeCount) * a.Price) AS UnDistributeAmount FROM dbo.View_PurchaseOrderDetail a left JOIN dbo.TB_Distribution b
            ON a.OrderDetailID=b.OrderDetailID 
            WHERE ISNULL(CenSubmiitTime,0)>0 AND (

            (StockType=0
	              AND ISNULL(PreOrderDetailID,'')=''
	              AND (
			             (ISNULL(WarehouseTime,'')<>'' AND datediff(ss,WarehouseTime,DATEADD(month,(DATEPART(MONTH,		CenSubmiitTime)),'2011-01-26')) > 0 )
			             OR (ISNULL(WarehouseTime,'')='' AND datediff(ss,GETDATE(),DATEADD(month,(DATEPART(MONTH,CenSubmiitTime)),'2011-01-26')) < 0 )
		               )	 
                  ) 
              OR (
 		            StockType=0 
 		            AND ISNULL(PreOrderDetailID,'')<>''
 		            AND (
 				             (ISNULL(WarehouseTime,'')<>'' AND
 				             datediff(ss,CenSubmiitTime,WarehouseTime) > 345600
 				             )
 				             OR
 				              (ISNULL(WarehouseTime,'')='' AND
 				             datediff(ss,GETDATE(),DATEADD(day,4,CenSubmiitTime)) < 0
 			                ) 
 	                    ) 
 	             )
              OR (
 		            StockType=1 
 		            AND ISNULL(PreOrderDetailID,'')<>''
 		            AND (
 				             (ISNULL(WarehouseTime,'')<>'' AND
 				             datediff(ss,CenSubmiitTime,WarehouseTime) > 345600
 				             )
 				             OR
 				              (ISNULL(WarehouseTime,'')='' AND
 				             datediff(ss,GETDATE(),DATEADD(day,4,CenSubmiitTime)) < 0
 			                ) 
 	                    ) 
 	             )

            )          
            {0} and a.AreaID like '{2}%' and a.CompanyName_PS like '%{3}%'

                GROUP BY a.CompanyID_PS 
             ORDER BY {1}", DAL_Helper.GetWhereCondition(where, false), where.SortFields,AreaID,comp);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        public static DataTable GetUnDistributeDetail(HZ.Supervise.Model.Query.UnDistributeCompanyQuery where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"SELECT a.OrderName,a.StartTime,a.PlanEndTime,a.ProductName,a.MedicineModelName,a.Pack,a.Unit,a.HospitalName,a.CenSubmiitTime,
a.CompanyName_SC,CompanyName_TB  ,b.CompanyValidateWarehouseTime,DATEADD(day,-2,b.CompanyValidateWarehouseTime) CompanyValidateWarehouseTime2daysago
FROM dbo.View_PurchaseOrderDetail a left JOIN dbo.TB_Distribution b
ON a.OrderDetailID=b.OrderDetailID 
WHERE ISNULL(CenSubmiitTime,0)>0 AND 
 ( (CenSubmiitTime IS NOT NULL AND datediff(ss,CenSubmiitTime,CompanyValidateWarehouseTime) > 172800 ) OR ( CenSubmiitTime IS NULL AND datediff(ss,CenSubmiitTime,GETDATE())>172800) OR (DATEDIFF(ss,CenSubmiitTime,GETDATE())>172800 AND DistributeTime IS NULL
                 )) {0} ", DAL_Helper.GetWhereCondition(where, true));
           
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        public static DataTable GetProcureCatalog(string productname,string _sortId, int currentpage, int pagesize, out int totalrecord)
        {
            string strwhere = "1=1";
            if (!string.IsNullOrEmpty(productname))
            {
                strwhere += string.Format(" and ProductName LIKE '%{0}%'", productname);
            }
            if (!string.IsNullOrEmpty(_sortId))
            {
                strwhere += string.Format(" and SortID='{0}'", _sortId);
            }
            string sql = string.Format("SELECT * FROM View_ProcureCatalog WHERE {0} ORDER BY ProductName", strwhere);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 条件查询采购目录信息（有效的【在采购项目有效期内】）
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static DataTable  GetProcureCatalogByWhere(HZ.Supervise.Model.View.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.View.ProcureCatalog>(procurecatalog);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT *,CASE WHEN IsUsing =0 THEN '不在线' ELSE '在线' END TradeState FROM View_ProcureCatalog_TB WHERE 1=1 {0}  ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return dt;
        }


        /// <summary>
        /// 条件查询采购目录信息（有效的【在采购项目有效期内】）
        /// </summary>
        /// <param name="procureCatalog">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns>所有采购目录信息</returns>
        public static IList<Model.View.ProcureCatalog> GetProcureCatalogByWhere(Model.Query.ProcureCatalog procurecatalog, int currentPage, int pageSize, out int totalRecord)
        {
            string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Query.ProcureCatalog>(procurecatalog);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_ProcureCatalog WHERE 1=1  and SortSource=1  {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return DAL_Helper.CommonFillList<HZ.Supervise.Model.View.ProcureCatalog>(dt);
        }

        /// <summary>
        /// 获取类别
        /// </summary>
        /// <param name="strsortname"></param>
        /// <returns></returns>
        public static DataTable GetSort(string strsortname, int currentPage, int pagesize, out int totalRecord)
        {
            string strssele ="";
            if (strsortname.Equals(""))
            {
                strssele = "select * from tb_sort order by sortID";
            }
            else
            {
                strssele = "select * from tb_sort where sortname like '%" + strsortname + "%' order by sortID";
            }

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strssele, pagesize);

            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            return pdes.GetSpecailPage();

            
        }
        /// <summary>
        /// 获取类别
        /// </summary>
        /// <param name="strsortname"></param>
        /// <returns></returns>
        public static DataTable GetPsympToms(string strwhere, int currentPage, int pagesize, out int totalRecord)
        {
            string strssele = "";
            if (strwhere != "")
            {
                strssele = "select distinct PsympToms from TB_Prescription where PsympToms like '%" + strwhere + "%' order by PsympToms";
            }
            else
            {
                strssele = "select distinct PsympToms from TB_Prescription order by PsympToms";
            }
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(strssele, pagesize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }

        /// <summary>
        /// 配送率48小时统计（高值）
        /// </summary> 
        public static DataTable GetCompanyRate(string strwhere, string timeform, string timeTo,string orderby, int currentPage, int pagesize, out int totalRecord)
        {
            string sql = string.Format(@"
                                                   SELECT *
                                                         FROM   ( SELECT    Com.*,
                                                                            ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,
                                                                            ISNULL(Dis.WarehouseAmount, 0) WarehouseAmount,
                                                                            case when ISNULL(Pur.PurchaseAmount, 0) = 0 then NULL
                                                                                 else CAST(ISNULL(Dis.WarehouseAmount, 0) * 100
                                                                                      / PurchaseAmount AS DECIMAL(18, 2))
                                                                            END Rate48
                                                                  FROM      ( SELECT CompanyID, CompanyName FROM TB_CompanyInfo A INNER JOIN dbo.TB_Role_User B ON A.CompanyID = B.UserID WHERE B.RoleID IN ('06','02') {2}
                                                                            ) Com
                                                                            LEFT JOIN ( SELECT  CompanyID_PS CompanyID,
                                                                                                SUM(PRICE * PurchaseCount) PurchaseAmount 
                                                                                        FROM    TB_PurchaseOrderDetail
                                                                                        WHERE   1 = 1 and SubmiTime>='{1}' and SubmiTime< '{0}'
                                                                                                AND OrderDetailState > 0
                                                                                        GROUP BY CompanyID_PS
                                                                                      ) Pur ON Com.CompanyID = Pur.CompanyID
                                                                            LEFT JOIN ( SELECT  CompanyID_PS CompanyID,
                                                                                                SUM(PRICE * WarehouseCount) WarehouseAmount
                                                                                        FROM    VIEW_Distribution
                                                                                        WHERE   1 = 1
                                                                                                and DistributeState > 0
                                                                                                and datediff(hour, DistributeTime,
                                                                                                             SubmiTime) <= 48
                                                                                                AND OrderDetailID IN (
                                                                                                SELECT  OrderDetailID
                                                                                                FROM    TB_PurchaseOrderDetail
                                                                                                WHERE   OrderDetailState > 0 )
                                                                                        GROUP BY CompanyID_PS
                                                                                      ) Dis ON Com.CompanyID = Dis.CompanyID
                                                                ) T
                                                         WHERE  Rate48 IS NOT NULL 
                                                         ORDER BY CompanyID
                                                    ", timeTo, timeform, strwhere, orderby);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }

        /// <summary>
        /// 配送率48小时统计（低值）
        /// </summary>  
        public static DataTable GetCompanyRate_DZ(string strwhere, string timeform, string timeTo, string orderby, int currentPage, int pagesize, out int totalRecord)
        {
            string sql = string.Format(@"
                                                   SELECT *
                                                         FROM   ( SELECT    Com.*,
                                                                            ISNULL(Pur.PurchaseAmount, 0) PurchaseAmount,
                                                                            ISNULL(Dis.WarehouseAmount, 0) WarehouseAmount,
                                                                            case when ISNULL(Pur.PurchaseAmount, 0) = 0 then NULL
                                                                                 else CAST(ISNULL(Dis.WarehouseAmount, 0) * 100
                                                                                      / PurchaseAmount AS DECIMAL(18, 2))
                                                                            END Rate48
                                                                  FROM      ( SELECT CompanyID, CompanyName FROM TB_CompanyInfo A INNER JOIN dbo.TB_Role_User B ON A.CompanyID = B.UserID WHERE B.RoleID IN ('06','02') {2}
                                                                            ) Com
                                                                            LEFT JOIN ( SELECT  CompanyID_PS CompanyID,
                                                                                                SUM(PRICE * PurchaseCount) PurchaseAmount 
                                                                                        FROM    TB_PurchaseOrderDetailDZ
                                                                                        WHERE   1 = 1 and SubmiTime>='{1}' and SubmiTime< '{0}'
                                                                                                AND OrderDetailState > 0
                                                                                        GROUP BY CompanyID_PS
                                                                                      ) Pur ON Com.CompanyID = Pur.CompanyID
                                                                            LEFT JOIN ( SELECT  CompanyID_PS CompanyID,
                                                                                                SUM(PRICE * WarehouseCount) WarehouseAmount
                                                                                        FROM    VIEW_DistributionDZ
                                                                                        WHERE   1 = 1
                                                                                                and DistributeState > 0
                                                                                                and datediff(hour, DistributeTime,
                                                                                                             SubmiTime) <= 48
                                                                                                AND OrderDetailID IN (
                                                                                                SELECT  OrderDetailID
                                                                                                FROM    TB_PurchaseOrderDetailDZ
                                                                                                WHERE   OrderDetailState > 0 )
                                                                                        GROUP BY CompanyID_PS
                                                                                      ) Dis ON Com.CompanyID = Dis.CompanyID
                                                                ) T
                                                         WHERE  Rate48 IS NOT NULL 
                                                         ORDER BY CompanyID
                                                    ", timeTo, timeform, strwhere, orderby);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }



        #region 参会相关
        /// <summary>
        /// 会议列表  
        /// </summary>  
        public static DataTable GetMeetList(string str, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select * from TB_Meet where 1=1 {0} order by MeetStartTime", str);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }
        /// <summary>
        /// 会议列表  
        /// </summary>  
        public static DataTable GetMeetList2(string str)
        {
            string sql = string.Format(@"select * from TB_Meet where 1=1 {0}", str);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }
        /// <summary>
        /// 新增/修改会议
        /// </summary>  
        public static int AddMeet(Model.Meet mt)
        {
            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            try
            {
                h.BeginTrans();

                if (string.IsNullOrEmpty(mt.MeetID))
                {
                    mt.MeetID = Guid.NewGuid().ToString();
                    mt.AddTime = DateTime.Now;
                    DAL_Helper.OperateTableSQL(mt, TableOperator.Insert);
                }
                else
                {
                    DAL_Helper.OperateTableSQL(mt, TableOperator.Update);
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                return 0;
            }
        }
        // <summary>
        /// 删除会议
        /// </summary>  
        public static int DelMeet(string mids)
        {
            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            try
            {
                h.BeginTrans();

                string[] midstr = mids.Split(',');
                for (int i = 0; i < midstr.Length; i++)
                {
                    if (Convert.ToInt32(h.ExecuteScalar(string.Format("select count(1) from TB_MeetInfo where MID='{0}'", midstr[i]))) == 0)
                    {
                        string sql = string.Format(@"delete from TB_Meet where MeetID='{0}'", midstr[i]);
                        h.ExecuteNonQuery(sql);
                    }
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                return 0;
            }
        }
        /// <summary>
        /// 参会人员列表  
        /// </summary>  
        public static DataTable GetMeetInfoList(string str, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select * from TB_MeetInfo where 1=1 {0} order by HosName,Name", str);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }
        /// <summary>
        /// 参会人员列表（中心端） 
        /// </summary>   
        public static DataTable GetMeetInfoListzbzx(string str, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select c.MeetName,MeetContent,MeetStartTime,MeetEndTime,a.*,b.AreaName from TB_MeetInfo a 
                inner join TB_Area b on a.CHAreaID=b.AreaID 
                inner join TB_Meet c on a.MID=c.MeetID where 1=1 {0} order by AreaName,HosName,Name", str);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }
        /// <summary>
        /// 更新参会人员信息
        /// </summary> 
        public static int UpdateCHRYInfo(HZ.Supervise.Model.MeetInfo mt)
        {
            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            try
            {
                h.BeginTrans();

                DAL_Helper.OperateTableSQL(mt, TableOperator.Update);

                h.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                return 0;
            }
        }
        /// <summary>
        /// 删除参会人员信息
        /// </summary>  
        public static int DelCHRYInfo(string ids)
        {
            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            try
            {
                h.BeginTrans();

                string sql = string.Format("delete from TB_MeetInfo where ID IN ({0})", ids);
                h.ExecuteNonQuery(sql);

                h.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                return 0;
            }
        }
        /// <summary>
        /// 医院列表  
        /// </summary>  
        public static DataTable GetHosList(string str, string mid)
        {
            string sql = string.Format(@"select * from (
            select a.HospitalID HID,a.HospitalName HName,AreaID from TB_Hospital a inner join TB_User b on a.HospitalID=b.UserID where IsAdmin=1 and IsUsing=1   
            union all 
            select a.UserID HID,a.UserName HName,AreaID from TB_User a inner join TB_Role_User b on a.UserID=b.UserID inner join TB_ManageOrg c on a.UserID=c.ManageOrgID
            where a.IsUsing=1 and a.IsAdmin=1 and b.RoleID='06' and SUBSTRING(AreaID,5,2)<>'00')tb 
            where 1=1 and HID not in (select HID from TB_MeetInfo where MID='{1}') {0} order by HID", str, mid);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }
        /// <summary>
        /// 保存医院参会人员信息  
        /// </summary>  
        public static int SaveHosMeet(List<HZ.Supervise.Model.MeetInfo> list)
        {
            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            try
            {
                h.BeginTrans();

                for (int i = 0; i < list.Count; i++)
                {
                    if (!string.IsNullOrEmpty(list[i].HID) && !string.IsNullOrEmpty(list[i].HosName) && !string.IsNullOrEmpty(list[i].MID))
                    {
                        h.ExecuteNonQuery(string.Format(@"insert into TB_MeetInfo (ID,JdjgID,CHAreaID,HID,HosName,Name,XB,Postion,TelNo,IsZS,AddTime,MID) 
                            values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}');",
                        Guid.NewGuid().ToString(), list[i].JdjgID, list[i].CHAreaID, list[i].HID, list[i].HosName, list[i].Name,
                        list[i].XB, list[i].Postion, list[i].TelNo, list[i].IsZS, DateTime.Now, list[i].MID));
                    }
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                return 0;
            }
        }
        /// <summary>
        /// 获取已上报人数
        /// </summary>  
        public static int GetHaveSubCount(string oid, string mid)
        {
            string sql = string.Format(@"select count(1) from TB_MeetInfo where JdjgID='{0}' and MID='{1}'", oid, mid);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql));
        }
        /// <summary>
        /// 获取培训需求  
        /// </summary>  
        public static DataTable GetPxContent(string oid, string mid)
        {
            string sql = string.Format(@"select * from TB_MeetPxContent where JID='{0}' and MID='{1}'", oid, mid);
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }
        /// <summary>
        /// 是否填写培训需求  
        /// </summary>  
        public static int IsPxContent(string oid, string mid)
        {
            string sql = string.Format(@"select count(1) from TB_MeetPxContent where JID='{0}' and MID='{1}'", oid, mid);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql));
        }
        /// <summary>
        /// 判断会议报名时间是否过时  1过时 0未过时
        /// </summary>  
        public static int IsBmGuoShi(string mid)
        {
            string sql = string.Format(@"select case when BaoMingEndTime>GETDATE() then 0 else 1 end isguoqi from TB_Meet where MeetID='{0}'", mid);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql));
        }
        /// <summary>
        /// 获取培训需求（中心端） 
        /// </summary>   
        public static DataTable GetPxContentzbzx(string str, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format(@"select MeetName,MeetContent,MeetStartTime,MeetEndTime,a.* from TB_MeetPxContent a inner join TB_Meet b on a.MID=b.MeetID where 1=1 {0} order by JID", str);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;
            return pdes.GetSpecailPage();
        }
        /// <summary>
        /// 填写培训需求
        /// </summary>  
        public static int SetPxContent(Model.MeetPxContent mt)
        {
            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            try
            {
                h.BeginTrans();

                string sql = "";
                if (Convert.ToInt32(h.ExecuteScalar(string.Format("select count(1) from TB_MeetPxContent where JID='{0}' and MID='{1}'", mt.JID, mt.MID))) == 0)
                {
                    sql = string.Format("insert into TB_MeetPxContent (CID,JID,MID,JName,ArID,Content,SubTime) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}');",
                        Guid.NewGuid().ToString(), mt.JID, mt.MID, mt.JName, mt.ArID, mt.Content, DateTime.Now);
                    h.ExecuteNonQuery(sql);
                }
                else
                {
                    sql = string.Format("update TB_MeetPxContent set Content='{0}' where JID='{1}' and MID='{2}';", mt.Content, mt.JID, mt.MID);
                    h.ExecuteNonQuery(sql);
                }

                h.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                return 0;
            }
        }
        #endregion
    }
}
